﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Data.SqlClient;
using System.Data;
using System.ComponentModel;
using System.Configuration;

namespace EWDTAssignment.App_Code
{
    
    public class LiveChatSQL
    {
        public static int Say(int accountID, int staffID, DateTime time, string message,string type)
        {
            string connectionString = "insert into Chat(AccountID,StaffID,Message,Time,Type) values(@accountID,@staffID,@message,@time,@type)";
            int a = CRUD.ExecuteNonQuery(connectionString, new SqlParameter("@accountID", accountID), new SqlParameter("@staffID", staffID), new SqlParameter("@message", message), new SqlParameter("@time", time),new SqlParameter("@type",type));
            return a;
        }
        [DataObjectMethod(DataObjectMethodType.Select)]
        public static DataTable uploadMessage(int accountID, DateTime time)
        {
            string connectionString = "select c.AccountID,c.StaffID,c.Message,c.Time,c.Type, a.Username from Chat c ,Account a where c.AccountID=@accountID and a.AccountID=c.AccountID and c.Time>@time Order by c.Time desc";
            DataTable dt = new DataTable();
            dt.Columns.Add("AccountID",typeof(int));
            dt.Columns.Add("StaffID", typeof(int));
            dt.Columns.Add("Message", typeof(string));
            dt.Columns.Add("Time", typeof(DateTime));
            dt.Columns.Add("Type", typeof(string));
            dt.Columns.Add("Username", typeof(string));

            using (SqlConnection conn = new SqlConnection(ConfigurationManager.ConnectionStrings["eShopDB"].ConnectionString))
            {
                conn.Open();
                using (SqlCommand comm = conn.CreateCommand())
                {
                    comm.CommandText = connectionString;
                    comm.Parameters.AddWithValue("@accountID", accountID);
                    comm.Parameters.AddWithValue("@time", time);
                    SqlDataReader dr = comm.ExecuteReader();
                    while (dr.Read())
                    {
                        DataRow newRow = dt.NewRow();
                        newRow["AccountID"]=(int)dr[0];
                        newRow["StaffID"] = (int)dr[1];
                        newRow["Message"] = (string)dr[2];
                        newRow["Time"]=(DateTime)dr[3];
                        newRow["Type"]=(string)dr[4];
                        newRow["Username"] = (string)dr[5];
                        dt.Rows.Add(newRow);
                    }
                }

            }
            return dt;
        }
        public static DataTable ShowNameList(DateTime day)
        {
            string connectionString = "select Username,AccountID from Account where AccountID in (select distinct AccountID from Chat where Time>@day)";
            DataTable dt = new DataTable();
            dt = CRUD.ExecuteReader(connectionString, new SqlParameter("@day", day));
            return dt;
        }
    }
}